class: center, middle, inverse, title-slide # Introduction to PostGIS ## Relational databases & GIS ### Andreas Scharmüller ### AG Landscape Ecology ### 2021-11-13 (updated: 2021-11-14) --- background-image: url(data:image/png;base64,#https://d1.awsstatic.com/rdsImages/postgresql_logo.6de4615badd99412268bc6aa8fc958a0f403dd41.png) background-position: 80% 80% ## What is PostGIS ? - It's FOSS! - Spatial extension for the relational database PostgreSQL - Compliant with the Open Geospatial Consortium (OGC) standards <br><https://www.ogc.org> - Simple Feature Access <br><https://en.wikipedia.org/wiki/Simple_Features> - `ST_Area()` - `ST_Length()` - `ST_Intersection()` - `ST_Intersects()` - `...`  --- background-image: url(data:image/png;base64,#https://upload.wikimedia.org/wikipedia/commons/7/7b/Logo_square_postgis.png) background-position: 80% 80% ## What is PostGIS ? - Spatial extension for the relational database PostgreSQL - Geospatial Data Type - Point - Line - Polygon - Geometry Collection - Raster - (Spatial) Indices  --- ## Why not use a Shapefile? .pull-left[ ### ArcGIS (proprietary)  ``` ## cities.dbf ## cities.prj ## cities.shp ## cities.shx ``` ] .pull-right[ ### PostGIS  - Geo-Information - __One table__ - __One Column__ ] --- ## Why not use a Shapefile? .pull-left[ <br><br> - Easy data storage (comapared on standard GIS software) - Standard simple feature formatting allows spatial data to be accessed by other software - Automation of processes - Handling of large data-sets ] .pull-right[ ### PostGIS  - Geo-Information - __One table__ - __One Column__ ] ---  Illustration (c) by [Allison Horst](https://github.com/allisonhorst/stats-illustrations/blob/master/rstats-artwork/sf.png) --- # Geo Libraries - [GDAL](https://gdal.org) - Geo Data Abstraction Library  - [GEOS](https://libgeos.org) - Geometry Engine, Open Source  - [PROJ](https://proj.org)  --- class: center, inverse, middle ## PostGIS functions --- ## Palar River Example  --- ## Palar River Example | length| pente| strahler|fid |geom | |-------:|---------:|--------:|:-------|:------------------------------| | 3053.50| 20.366169| 1|1194653 |LINESTRING (4550796 2582247... | | 986.99| 5.996377| 2|1194668 |LINESTRING (4553302 2582898... | | 476.30| 11.744198| 2|1194670 |LINESTRING (4552910 2583143... | <!-- --> --- ## Union ```sql SELECT ST_Union(geom) AS geom FROM palar ``` ```r palar |> st_union() ``` <!-- --> --- ## Length ```sql SELECT ST_Length(ST_Union(geom)) geom FROM palar ``` ```r palar |> st_union() |> st_length() ``` ``` ## 23289.93 [m] ``` --- ## Buffer - 100m ```sql SELECT ST_Buffer(ST_Union(geom), 100) AS geom_buf FROM palar ``` ```r palar |> st_union() |> st_buffer(100) ``` <!-- --> --- ## Area ```sql SELECT ST_Area(ST_Buffer(ST_Union(geom), 100)) AS area FROM palar ``` ```r palar |> st_union() |> st_buffer(100) |> st_area() ``` ``` ## 4615553 [m^2] ``` --- ## Convex Hull ```sql SELECT ST_ConvexHull(geom) AS geom FROM palar ``` ```r palar |> st_union() |> st_convex_hull() ``` <!-- --> --- ## Projections ```sql SELECT ST_SRID(geom) FROM palar LIMIT 1 ``` ```r st_crs(palar) # 3035 ``` ### Transform ```sql CREATE TABLE palar2 AS SELECT fid, ST_Transform(geom, 4326) geom -- EPSG for WGS84 FROM palar ``` <https://epsg.io/4326> --- ## Intersection - Aim: Intersect 500m River Buffer with [CORINE - LULC](https://land.copernicus.eu/pan-european/corine-land-cover) data <!-- --> --- ## Intersection ```sql SELECT cor.label, cor.label, ST_Intersection(ST_Buffer(riv.geom, 500), cor.geom) geom FROM palar riv, corine cor WHERE ST_DWithin(tbl1.geom, tbl2.geom, 500) ``` <!-- --> --- ## Intersection ```r st_intersection(st_buffer(st_union(palar), 500), corine) ``` --- class: center, inverse, middle # Idexing --- ## Index - Extremely important for performance! - Different Indices for different data types - PRIMARY KEY - BTREE - __GIST__ - Geographic Data  <http://postgis.net/workshops/postgis-intro/indexing.html> --- ## Index --- ## Resources - R sf cheatsheet <https://raw.githubusercontent.com/rstudio/cheatsheets/main/sf.pdf> - Gentle Introduction to PostGIS <https://medium.com/innovation-and-technology/part-1-postgis-at-the-city-of-boston-9476293d71c2> <https://medium.com/@paylakatel/part-2-postgis-at-the-city-of-boston-711cf30cf1f3> <https://medium.com/@paylakatel/part-3-postgis-at-the-city-of-boston-98b83b0d1503> --- ## Slides - OLAT - <https://andschar.github.io/teaching/PostGIS-intro.html> ### Made with - <https://github.com/rstudio/rmarkdown> - <https://github.com/yihui/knitr> - <https://github.com/yihui/xaringan> --- layout: false # [Introduction to Git & GitHub](https://andschar.github.io/teaching/GIT.html) ## Thank you for your attention! ### Material: <https://andschar.github.io/teaching> __Andreas Scharmüller__<br> Quantitative Landscape Ecology<br/> iES Landau, Institute for Environmental Sciences<br> University of Koblenz-Landau<br> University of Strasbourg <img src="data:image/png;base64,#https://upload.wikimedia.org/wikipedia/de/thumb/9/9f/Twitter_bird_logo_2012.svg/1259px-Twitter_bird_logo_2012.svg.png" width="20"> @andschar <br/> <img src="https://seeklogo.com/images/M/mail-icon-logo-28FE0635D0-seeklogo.com.png" width="20"> andschar@protonmail.com <img src="https://www.uni-koblenz-landau.de/de/landau/fb7/umweltwissenschaften/logos/land-ecol-neu.jpg" width="100"> <img src="https://www.uni-koblenz-landau.de/de/uni/organisation/verwaltung/abteilungen/abt-1/dokumente-ab1/uni-logo-farbig.jpg" width="200">